package cn.nawang.ebeim.client.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.nawang.ebeim.client.bean.NetWorkMonitorBean;

public class NetWorkMonitorDbUtil {
  private static final String SQL_TABLE_NET_WORK_MONITOR = "create table if not exists net_work_monitor("
      + "id integer primary key autoincrement, average_time number, packet_loss_rate number,"
      + "status varchar(10), url varchar(50))";
  private static final String SQL_INSERT_NET_WORK_MONITOR = "insert into net_work_monitor(average_time,packet_loss_rate,status,url) values(?,?,?,?)";
  
  private static final String SQL_SELECT_NET_WORK_MONITOR =
      "select * from net_work_monitor group by url order by id asc";

  private static Connection connection = null;

  public static void initConnection() throws SQLException {
    String db = FileUtil.getNetWorkMonitor();
    if(connection == null){
      connection = DriverManager.getConnection("jdbc:sqlite:" + db);
    }
  } 

  public static void initDatabase() throws SQLException {
    Statement stmt = connection.createStatement();
    stmt.execute(SQL_TABLE_NET_WORK_MONITOR);
    stmt.close();
  }
  
  public static synchronized void addNetWorkMonitorBean(NetWorkMonitorBean bean) {
    PreparedStatement stmt = null;
    try {
      stmt = connection.prepareStatement(SQL_INSERT_NET_WORK_MONITOR);
      stmt.setDouble(1, bean.getAverageTime());
      stmt.setDouble(2, bean.getPacketLossRate());
      stmt.setString(3, bean.getStatus());
      stmt.setString(4, bean.getUrl());
      stmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt);
    }
  }
  
  public static synchronized List<NetWorkMonitorBean> getNetWorkMonitorBean() {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    NetWorkMonitorBean bean = null;
    List<NetWorkMonitorBean> list =null;
    try {
      list=new ArrayList<NetWorkMonitorBean>();
      stmt = connection.prepareStatement(SQL_SELECT_NET_WORK_MONITOR);
      rs = stmt.executeQuery();
      while (rs.next()) {
        bean = new NetWorkMonitorBean();
        int _id= rs.getInt("id");
        double averageTime= rs.getDouble("average_time");
        double packetLossRate= rs.getDouble("packet_loss_rate");
        String status= rs.getString("status");
        String url= rs.getString("url");
        bean.setId(_id);
        bean.setAverageTime(averageTime);
        bean.setPacketLossRate(packetLossRate);
        bean.setStatus(status);
        bean.setUrl(url);
        list.add(bean);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(stmt, rs);
    }
    return list;
  }
  
  public static void close(PreparedStatement stmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(PreparedStatement stmt) {
    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close() {

    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }


}
